﻿using System;
using Code;
using System.Data;
using System.Data.Common;


namespace E2_WEBSITE.Code.Admin
{
    public class NewsDataAccess
    {
        public DataTable GetNews()
        {
            try
            {
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = "SELECT t.id ID, t.TieuDe TieuDe, t.NoiDung NoiDung, u.Ten NguoiDang, t.NgayDang NgayDang FROM TIN_TUC t, NGUOI_DUNG u WHERE t.idNguoiDung = u.id";
                return provider.ExecuteQueryDataTable(sql);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public NewsInfo GetNewsInfo(int id)
        {
            try
            {
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = string.Format("SELECT t.id ID, t.TieuDe TieuDe, t.NoiDung NoiDung, t.idNguoiDung idNguoiDung, u.Ten NguoiDang, t.NgayDang NgayDang FROM TIN_TUC t, NGUOI_DUNG u WHERE t.id = {0} AND t.idNguoiDung = u.id", id);
                DataTable data = provider.ExecuteQueryDataTable(sql);

                if (data.Rows.Count > 0)
                {
                    return NewsInfo.Load(data);
                }

                return null;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool InsertNews(int nid, int UserId, NewsInfo news)
        {
            try
            {
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = string.Format("INSERT INTO [COMPUTERSHOPDB].[dbo].[TIN_TUC] " +
                                               "([id]" +
                                               ",[TieuDe]" +
                                               ",[NoiDung]" +
                                               ",[idNguoiDung]" +
                                               ",[NgayDang]) " +
                                         "VALUES " +
                                               "({0}" +
                                               ",N'{1}'" +
                                               ",N'{2}'" +
                                               ",{3}" +
                                               ",'{4}')",
                                               nid,
                                               news.Title,
                                               news.Content,
                                               UserId,
                                               news.PublishDay.ToString(Utilities.DateTimeFormatString));

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public int GetNewsFreeId()
        {
            try
            {
                int nid = -1;
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = string.Format("SELECT MAX([id]) as ID " +
                                                "FROM [COMPUTERSHOPDB].[dbo].[TIN_TUC]");
                DataTable table = provider.ExecuteQueryDataTable(sql);
                nid = int.Parse(table.Rows[0]["ID"].ToString()) + 1;
                return (nid);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool UpdateNews(int nid, int UserId, NewsInfo news)
        {
            try
            {
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = string.Format("UPDATE [COMPUTERSHOPDB].[dbo].[TIN_TUC] SET [TieuDe] = N'{1}', [NoiDung] = N'{2}', [idNguoiDung] = {3}, [NgayDang] = '{4}' WHERE [id] = {0}",
                    nid,
                    news.Title,
                    news.Content,
                    UserId,
                    news.PublishDay.ToString(Utilities.DateTimeFormatString));

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public bool RemoveNews(int nid)
        {
            try
            {
                DataProvider provider = DataProvider.GlobalDataProvider;
                string sql = string.Format("DELETE FROM TIN_TUC WHERE id = {0}", nid);

                return (provider.ExecuteNonQuery(sql, true) > 0);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
